﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using Marbles.Core.Connectors;

namespace Marbles.Core.Content.Profiles
{
    public class ProfileQuestion
    {

        public string SiteId { get { return _siteid; } }
        public Int32 QuestionId { get { return _questionid; } }
        public string Description { get { return _description; } set { _description = value; } }
        public string ShortDescription { get { return _shortdescription; } set { _shortdescription = value; } }
        public ProfileQuestionType QuestionType { get { return _questiontype; } set { _questiontype = value; } }
        public Int32 QuestionTypeValue { get { return Convert.ToInt32(_questiontype); } set { _questiontype = (ProfileQuestionType)value; } }
        public string QuestionTypeName
        {
            get { return ProfileQuestionCollection.GetProfileQuestionTypes()[QuestionTypeValue]; }
        }

        private string _siteid = string.Empty;
        private string _shortdescription = string.Empty;
        private Int32 _questionid = 0;
        private string _description = string.Empty;
        private ProfileQuestionType _questiontype = ProfileQuestionType.Text;

        public ProfileQuestion()
        {
            _shortdescription = string.Empty;
            _siteid =string.Empty;
            _questionid = 0;
            _description = string.Empty;
            _questiontype = ProfileQuestionType.Text;
        }

        internal ProfileQuestion(Int32 SiteId, Int32 QuestionId)
            : this()
        {
            DataConnection dbc = new DataConnection();
            Command cmd = new Command("SELECT * FROM profiles WHERE siteid = @siteid AND questionid = @questionid");
            cmd.AddParameter("@siteid", _siteid);
            cmd.AddParameter("@questionid", _questionid);
            DataTable dt = dbc.FetchDataTable(cmd);

            if (dt.Rows.Count > 0)
            {
                _siteid = dt.Rows[0]["siteid"].ToString();
                _questionid = Convert.ToInt32(dt.Rows[0]["questionid "]);
                _description = dt.Rows[0]["description"].ToString();
                _shortdescription = dt.Rows[0]["shortdescription"].ToString();

                foreach (string s in Enum.GetNames(typeof(ProfileQuestionType)))
                {
                    if (s.ToLower() == dt.Rows[0]["questiontype"].ToString().ToLower())
                    {
                        _questiontype = (ProfileQuestionType)Enum.Parse(typeof(ProfileQuestionType), dt.Rows[0]["questiontype"].ToString());
                        break;
                    }
                }

            }

        }

        internal ProfileQuestion(DataRow row)
            : this()
        {
            _siteid = row["siteid"].ToString();
            _questionid = Convert.ToInt32(row["questionid"]);
            _description = row["description"].ToString();
            _shortdescription = row["shortdescription"].ToString();

            foreach (string s in Enum.GetNames(typeof(ProfileQuestionType)))
            {
                if (s.ToLower() == row["questiontype"].ToString().ToLower())
                {
                    _questiontype = (ProfileQuestionType)Enum.Parse(typeof(ProfileQuestionType), row["questiontype"].ToString());
                    break;
                }
            }
        }

        public static ProfileQuestion CreateNew(string SiteId)
        {
            ProfileQuestion q = new ProfileQuestion();
            q._siteid = SiteId;
            return q;
        }

        public void Insert()
        {
            if (Exists()) { Update(); }
            DataConnection dbc = new DataConnection();
            Command cmd = new Command("BEGIN TRANSACTION; DECLARE @questionid int;SET @questionid = 0;SELECT TOP 1 @questionid = questionid FROM profiles WHERE siteid = @siteid ORDER BY questionid DESC; SET @questionid = @questionid + 1; INSERT INTO profiles (siteid, questionid, description, questiontype, shortdescription) VALUES (@siteid, @questionid, @description, @questiontype, @shortdescription);SELECT @questionid AS questionid;COMMIT TRANSACTION");
            cmd.AddParameter("@shortdescription", _shortdescription);
            cmd.AddParameter("@siteid", _siteid);
            cmd.AddParameter("@description", _description.Trim());
            cmd.AddParameter("@questiontype", Enum.GetName(typeof(ProfileQuestionType), _questiontype));
            DataTable dt = dbc.FetchDataTable(cmd);

            if (dt.Rows.Count > 0) { _questionid = Convert.ToInt32(dt.Rows[0]["questionid"]); }
        }

        public void Update()
        {
            if (!Exists()) { Insert(); }
            DataConnection dbc = new DataConnection();
            Command cmd = new Command("UPDATE profiles SET shortdescription = @shortdescription, description = @description, questiontype = @questiontype WHERE siteid = @siteid AND questionid = @questionid");
            cmd.AddParameter("@siteid", _siteid);
            cmd.AddParameter("@shortdescription", _shortdescription);
            cmd.AddParameter("@questionid", _questionid);
            cmd.AddParameter("@description", _description.Trim());
            cmd.AddParameter("@questiontype", Enum.GetName(typeof(ProfileQuestionType), _questiontype));
            dbc.ExecuteCommand(cmd);
        }


        public bool Exists()
        {
            DataConnection dbc = new DataConnection();
            Command cmd = new Command("SELECT questionid FROM profiles WHERE siteid = @siteid AND questionid = @questionid");
            cmd.AddParameter("@siteid", _siteid);
            cmd.AddParameter("@questionid", _questionid);
            DataTable dt = dbc.FetchDataTable(cmd);
            return dt.Rows.Count > 0;
        }

        public void Delete()
        {
            DataConnection dbc = new DataConnection();
            Command cmd = new Command("DELETE FROM profiles WHERE siteid = @siteid AND questionid = @questionid");
            cmd.AddParameter("@siteid", _siteid);
            cmd.AddParameter("@questionid", _questionid);
            dbc.ExecuteCommand(cmd);
            cmd = new Command("DELETE FROM userprofiles WHERE siteid = @siteid AND questionid = @questionid");
            cmd.AddParameter("@siteid", _siteid);
            cmd.AddParameter("@questionid", _questionid);
            dbc.ExecuteCommand(cmd);
        }
    }

}